package analyzingSysetm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import S2.server.ConnectionManage;

public class analyzingsystem {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	private static  String[] cido;
	private static int[] numOfOrders;
	private static int[] totalprice;
	public static void updateClientImportance() throws SQLException{
		int cnt=0;
		String sql = "SELECT clientId,COUNT(*),SUM(totalprice) FROM ORDER"
				+ "GROUP BY clientId";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			rs = stmt.executeQuery();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		while(rs.next()){
			cnt++;
		}
		cido=new String[cnt];
		numOfOrders=new int[cnt];
		totalprice=new int[cnt];
		int []sekhem=new int[cnt];
		int i=0;
		/*int j;
		int tmp1;
		String tmp2;*/
		while(rs.next()){
			cido[i]=rs.getString(1);
			numOfOrders[i]=rs.getInt(2);
			totalprice[i]=rs.getInt(3);
			i++;
		}
		for(i=0;i<cnt;i++)
		{
			sekhem[i]=(int) (numOfOrders[i]*0.5+totalprice[i]*0.5);
		}
		int maxClient=sekhem[0];
		for(i=0;i<cnt;i++)
		{
			if(sekhem[i]>maxClient)
				maxClient=sekhem[i];
		}
			
		rs = null;
		float rate;
		int realRate;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
		for(i=0;i<cnt;i++)
		{
			sql = "UPDATE customer SET rate=? WHERE clientId=?";
			rate=(sekhem[i]/maxClient)*10;
			realRate=(int)rate;
			stmt.setInt(1,realRate);
			stmt.setString(2, cido[i]);
			rs = stmt.executeQuery();
		}	
		 }catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				rs.close();
			}
		}
		
	}
public static void updateInterestLevel() throws SQLException{
	float interestLevel;
	int realInterestLevel;
	String sql = "SELECT customerId,COUNT(*) FROM contactcustomer WHERE acceptAppeal=TRUE"
			+ "GROUP BY customerId";
	String sql2 = "SELECT customerId,COUNT(*) FROM contactcustomer WHERE acceptAppeal=FALSE"
			+ "GROUP BY customer	Id";
	ResultSet rs = null;
	ResultSet rs2 = null;
	try (PreparedStatement stmt = conn.prepareStatement(sql);) {
		rs = stmt.executeQuery();
	} 
	try (PreparedStatement stmt2 = conn.prepareStatement(sql2);) {
		rs2 = stmt2.executeQuery();
	} 
	catch (SQLException e) {
		
		e.printStackTrace();
	}
	int cnt = 0;
	while(rs.next()){
		cnt++;
	}
	String[] cid=new String[cnt];
	int[] numOfAccepts=new int[cnt];
	int[] numOfDenies=new int[cnt];
	int i=0;
	while(rs.next()){
		cid[i]=rs.getString(1);
		numOfAccepts[i]=rs.getInt(2);
		i++;
	}
	String tcid=null;
	while(rs2.next()){
		tcid=rs2.getString(1);
		for(i=0;i<cnt;i++){
			if(cid[i].compareTo(tcid)==0){
				numOfDenies[i]= rs2.getInt(2);
				break;
			}
		}
	}
	try {
		for(i=0;i<cnt;i++)
		{
			sql = "UPDATE customer SET interestlevel=? WHERE clientId=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			interestLevel=(numOfAccepts[i]/numOfDenies[i])*10;
			realInterestLevel=(int)interestLevel;
			stmt.setInt(1,realInterestLevel);
			stmt.setString(2, cido[i]);
			rs = stmt.executeQuery();
		}	
	}
		catch (SQLException e) {
			e.printStackTrace();
		}
	finally {
		if (rs != null) {
			rs.close();
			
		}
		if(rs2!=null){
			rs2.close();
		}
		
	}
}
}

